<?php
/**
 * 升級教師資料表
 * 新增資料到 users 、teacher 、group (預設為教師)
 *
 */
class Default_Model_Upgrade_TeacherBase extends Default_Model_Upgrade_Core
{
    protected $_table= 'teacher_base';
    protected $_targetTable = array('semester_teacher');

    function upgrade()	{
        if ($this->isUpgrade()) {
            //				echo 'ppppp';
            return 'isUpgrde';

        }
        // 先設為 MYISA
        // 更改為 INNOD
        $query = "ALTER TABLE `teacher` DROP FOREIGN KEY `teacher_ibfk_1` ;";
        $this->_newdbPdo->exec($query);
        $query = "ALTER TABLE `teacher`  ENGINE = MYISAM";
        $this->_newdbPdo->exec($query);

        $query ="ALTER TABLE `semester_teacher` DROP FOREIGN KEY `semester_teacher_ibfk_1` ;";
        $this->_newdbPdo->exec($query);
        $query ="ALTER TABLE `semester_teacher` DROP FOREIGN KEY `semester_teacher_ibfk_2` ;";
        $this->_newdbPdo->exec($query);
        $query = "ALTER TABLE `semester_teacher`  ENGINE = MYISAM";
        $this->_newdbPdo->exec($query);

//
//        $semester_duration = new 	Semester_Model_SemesterDuration();
//        // 學期設定
//        $semester =  $semester_duration->get_curr_year_seme();
//        $year = $semester['year'];
//        $semester = $semester['semester'];
        $date =date("Y-m-d");
        $query = "SELECT  * FROM school_day WHERE
         day_kind='start' AND day  < '$date' ORDER BY day DESC LIMIT 1";
         $res = $this->_sfs3Pdo->query($query)->fetch(PDO::FETCH_ASSOC);
         $year = $res['year'];
         $semester = $res['seme'];

        // 升級學校處室
        $tt = new Default_Model_Upgrade_SchoolRoom();
        $tt->upgrade();
        // 升級教師職稱
        $tt = new Default_Model_Upgrade_TeacherTitle();
        $tt->upgrade();

        // 升級至 users

        $success_count = 0;
        $error_count = 0;
        $query = "SELECT * FROM teacher_base";

        $res  = $this->_sfs3Pdo->query($query, PDO::FETCH_ASSOC);
        foreach($res as $row) {
            $arr = array();
            $arr['username'] = $this->iconv($row['teach_id']);
            $arr['name'] = $this->iconv($row['name']);
            // TODO 更改回 原MD5 格式 , 原資料少一碼
//            $password = substr($row['login_pass'],-9).substr($row['login_pass'],0,-10);
//            $arr['password'] = $password;
          $arr['password'] = $row['login_pass'];
            $find = $this->_newdbPdo->query("SELECT username FROM users WHERE username='".$arr['username']."'");
            if ($find->rowCount()>0) {
                continue;
            }
            $query  = "INSERT INTO  users  (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
            if ( $this->_newdbPdo->exec($query))
            $success_count++ ;
            else {
                $log['error_row']['users'][] = $row['username'];
                $error_count++;
            }
        }
        $log['success_count']['user'] = $success_count;


        $query = "SELECT a.* ,a.teach_id as username, b.* FROM teacher_base a LEFT JOIN teacher_post b ON a.teacher_sn=b.teacher_sn";
         $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);

        // 升級至 teacher
        $success_count = 0;
        foreach($res as $row) {
            $arr = array();
            $arr2 =array();
            $arr['username'] = $this->iconv($row['username']);
            $find = $this->_newdbPdo->query("SELECT username FROM teacher WHERE username='".$arr['username']."'");
            if ($find->rowCount() > 0) {
                continue;
            }
            $arr['identification_number'] = $this->iconv($row['teach_person_id']);
            $arr['sex'] = ($row['sex'] == 1)?'M':'F';
            $arr['birthday'] = $row['birthday'];
            $arr['born_place'] = $row['birth_place'];
            $arr['marriaged'] = $row['marriage'];
            $arr['address'] = $this->iconv($row['address']);
            $arr['phone_home'] = $this->iconv($row['home_phone']);
            $arr['phone_mobile'] = $this->iconv($row['cell_phone']);
            $arr['education_level'] = $row['teach_edu_kind'];
            $arr['overseas'] = ($row['teach_edu_abroad']==1)?'Y':'N';
            $arr['certification_type'] = $row['teach_check_kind'];
            $arr['certification_number'] = $this->iconv($row['teach_check_word']);
            $arr['certification_subject'] = $this->iconv($row['teach_sub_kind']);
            $arr['disability'] = ($row['teach_is_cripple']==1)?'Y':'N';
            $arr['condition_state'] = $row['teach_condition'];
            $arr['appoint_date'] =$row['appoint_date'];
            $arr['arrive_date'] =$row['arrive_date'];
            $arr['approve_date'] = $row['approve_date'];
            $arr['approve_number'] = $this->iconv($row['approve_number']);
            $arr['post_number'] = $this->iconv($row['post_num']);
            $arr['bywork_number'] = $this->iconv($row['bywork_num']);
            // 加入現在職別 現在職稱 現在處室, 做為授權認證判別欄位
            $arr['current_kind_sn'] = $row['post_kind'];
            $arr['current_title_sn'] = $row['teach_title_id'];
            $arr['current_office_id'] =$row['post_office'];
            $arr['sn'] = $row['teacher_sn'];
            $query  = "INSERT INTO  teacher (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";

            $resflag =  $this->_newdbPdo->exec($query);

            if ($resflag  and $row['teach_condition'] == 0){
                // 教師學期任職資料
                $arr2['year'] = $year;
                $arr2['semester'] = $semester;
                $arr2['teacher_id'] = $row['username'];
                $arr2['kind_sn'] = $row['post_kind'];
                $arr2['title_sn'] = $row['teach_title_id'];
                $arr2['office_id'] = $row['post_office'];
                $arr2['post_grade'] = $row['post_num'];
                $arr2['salary_grade'] = $row['salay'];
                if ($row['class_num'])
                $arr2['class_num'] = (int)substr($row['class_num'],0,1).'-'. (int)substr($row['class_num'],1);
                $query  = "INSERT INTO  semester_teacher (".implode(',',array_keys($arr2)).") VALUES('".implode("','",array_values($arr2))."')";
                $this->_newdbPdo->exec($query);
                $arr2 = array();
                // 加入教師群組
                $arr2['role_id'] = 'Teacher';
                $arr2['username'] = $row['username'];
                $query  = "INSERT INTO  groups (".implode(',',array_keys($arr2)).") VALUES('".implode("','",array_values($arr2))."')";
                $this->_newdbPdo->exec($query);
                // 加入 處室群
                $room_res = $this->_newdbPdo->query("SELECT name FROM school_room WHERE sn='{$row['post_office']}'",PDO::FETCH_ASSOC)->fetch();
                if ($room_res) {
                    $arr_room= array();
                    $arr_room['role_id'] = $room_res['name'];
                    $arr_room['username'] = $row['username'];
                    $query  = "INSERT INTO  groups (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                    $this->_newdbPdo->exec($query);
                }
                // 加入職稱
                $room_res = $this->_newdbPdo->query("SELECT name FROM teacher_title WHERE sn='{$row['teach_title_id']}'",PDO::FETCH_ASSOC)->fetch();
                if ($room_res) {
                    $arr_room= array();
                    $arr_room['role_id'] = $room_res['name'];
                    $arr_room['username'] = $row['username'];
                    $query  = "INSERT INTO  groups (".implode(',',array_keys($arr_room)).") VALUES('".implode("','",array_values($arr_room))."')";
                    $this->_newdbPdo->exec($query);
                }
                $success_count++ ;
            }
            else {
                $log['error_row']['teacher'][] = $row['username'];
                $error_count++;
            }

        }
        // 更改為 INNODB
        $query = "ALTER TABLE `teacher`  ENGINE = INNODB";
        $this->_newdbPdo->exec($query);
        $query = "ALTER TABLE `teacher` ADD FOREIGN KEY ( `username` ) REFERENCES `users` (
`username`) ON DELETE CASCADE ON UPDATE CASCADE ;";
        $this->_newdbPdo->exec($query);

        $query = "ALTER TABLE `semester_teacher`  ENGINE = INNODB";
        $this->_newdbPdo->exec($query);

        $query ="ALTER TABLE `semester_teacher` ADD FOREIGN KEY ( `teacher_id` ) REFERENCES `users` (
`username`) ON DELETE CASCADE ON UPDATE CASCADE ;";
        $this->_newdbPdo->exec($query);

        $query ="ALTER TABLE `semester_teacher` ADD FOREIGN KEY ( `title_sn` ) REFERENCES `teacher_title` (
`sn`) ON DELETE CASCADE ON UPDATE CASCADE ;";
        $this->_newdbPdo->exec($query);


        // 清除 acl cache
        $cache = Zend_Registry::get('cache');
        $cache->remove('acl_cache');

        //TODO 教師個人資料加入群組
        $log['success_count'] = $success_count;
        $log['error_count'] = $error_count;
        $log['memo'] = 'SFS3 教師資料升級';
        $log['upgrade_time']	= date("Y-m-d h:i:s");
        $this->finishUpgrade($log);
    }
}
